﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Reflection;
using System.Windows.Forms;

namespace HYFamilyLibDataManager
{
	public class SqliteFamilyTable : IFamilyTable
	{
		public SqliteFamilyTable(SqliteDatabase db)
		{
			this.mDatabase = db;
		}

		public bool Add(DbCategory category, DbFamily family, DbTransaction tr)
		{
			string keyName = family.KeyName;
			if (string.IsNullOrEmpty(keyName))
			{
				keyName = category.KeyName;
			}
			bool result = false;
			byte[] array = null;
			if (family.Image != null)
			{
				MemoryStream memoryStream = new MemoryStream();
				family.Image.Save(memoryStream, ImageFormat.Jpeg);
				array = memoryStream.ToArray();
			}
			try
			{
				using (DbCommand dbCommand = this.mDatabase.Connection.CreateCommand())
				{
					dbCommand.Transaction = tr;
					int num;
					if (family.Limit)
					{
						num = 1;
					}
					else
					{
						num = 0;
					}
					dbCommand.CommandText = string.Format("insert into [FamilyTable]([FamilyName],[FamilyImg],[ParentId],[Limit],[Version],[Description],[GUID],[Date],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count]) values ('{0}',{1},'{2}','{3}','{4}','{5}','{6}',{7},'{8}',{9},{10},'{11}','{12}','{13}','{14}')", new object[]
					{
						family.Name,
						"@img",
						category.Guid.ToString(),
						num,
						family.Version,
						family.Description,
						family.Guid.ToString(),
						"@date",
						keyName,
						family.ExData.Eid1,
						family.ExData.Eid2,
						family.ExData.Tag1,
						family.ExData.Tag2,
						family.Check,
						0
					});
					object value = this.mDatabase.Sqlite.CreateInstance(SqliteDatabase.SQLiteParameter, true, BindingFlags.CreateInstance, null, new object[]
					{
						"@img",
						array
					}, null, new object[0]);
					dbCommand.Parameters.Add(value);
					object value2 = this.mDatabase.Sqlite.CreateInstance(SqliteDatabase.SQLiteParameter, true, BindingFlags.CreateInstance, null, new object[]
					{
						"@date",
						family.Date
					}, null, new object[0]);
					dbCommand.Parameters.Add(value2);
					dbCommand.ExecuteNonQuery();
					family.KeyName = category.KeyName;
					family.SetDatabase(this.mDatabase);
				}
				result = true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			return result;
		}

		public int Count(CheckType checkType)
		{
			string sqlStr;
			if (checkType == CheckType.All)
			{
				sqlStr = string.Format("select [GUID] from [FamilyTable]", Array.Empty<object>());
			}
			else if (checkType == CheckType.Checked)
			{
				sqlStr = string.Format("select [GUID] from [FamilyTable] where [Check]='{0}'", 1);
			}
			else
			{
				sqlStr = string.Format("select [GUID] from [FamilyTable] where [Check]=!'{0}'", 1);
			}
			DataTable dataTable = this.mDatabase.ExecQuery(sqlStr);
			if (dataTable != null)
			{
				return dataTable.Rows.Count;
			}
			return 0;
		}

		public int Count(Parent parent, CheckType checkType)
		{
			string sqlStr;
			if (checkType == CheckType.All)
			{
				sqlStr = string.Format("select [GUID] from [FamilyTable] where [ParentId]='{0}'", parent.Guid.ToString());
			}
			else if (checkType == CheckType.Checked)
			{
				sqlStr = string.Format("select [GUID] from [FamilyTable] where [ParentId]='{0}' and [Check]='{1}'", parent.Guid.ToString(), 1);
			}
			else
			{
				sqlStr = string.Format("select [GUID] from [FamilyTable] where [ParentId]='{0}' and [Check]=!'{1}'", parent.Guid.ToString(), 1);
			}
			DataTable dataTable = this.mDatabase.ExecQuery(sqlStr);
			if (dataTable != null)
			{
				return dataTable.Rows.Count;
			}
			return 0;
		}

		public bool Update(DbFamily family)
		{
			int num = 0;
			if (family.Limit)
			{
				num = 1;
			}
			int num2 = 0;
			if (family.Check)
			{
				num2 = 1;
			}
			bool result;
			using (DbCommand dbCommand = this.mDatabase.Connection.CreateCommand())
			{
				dbCommand.CommandText = string.Format("update [FamilyTable] set [FamilyName]='{0}',[ParentId]='{1}',[Limit]='{2}',[Version]='{3}',[Description]='{4}',[FamilyImg]={5},[KeyName]='{6}',[eID1]={7},[eID2]={8},[tag1]='{9}',[tag2]='{10}',[Date]='{11}',[Check]='{12}',[Count]='{13}',[UseDate]='{14}' where [GUID]='{15}'", new object[]
				{
					family.Name,
					family.ParentId.ToString(),
					num,
					family.Version,
					family.Description,
					"@img",
					family.KeyName,
					family.ExData.Eid1,
					family.ExData.Eid2,
					family.ExData.Tag1,
					family.ExData.Tag2,
					family.Date.ToString("yyyy-MM-dd HH:mm:ss"),
					num2,
					family.Count,
					family.UseDate.ToString("yyyy-MM-dd HH:mm:ss"),
					family.Guid.ToString()
				});
				byte[] array = null;
				if (family.Image != null)
				{
					MemoryStream memoryStream = new MemoryStream();
					family.Image.Save(memoryStream, ImageFormat.Jpeg);
					array = memoryStream.ToArray();
				}
				object value = this.mDatabase.Sqlite.CreateInstance(SqliteDatabase.SQLiteParameter, true, BindingFlags.CreateInstance, null, new object[]
				{
					"@img",
					array
				}, null, new object[0]);
				dbCommand.Parameters.Add(value);
				if (dbCommand.ExecuteNonQuery() != -1)
				{
					result = true;
				}
				else
				{
					result = false;
				}
			}
			return result;
		}

		public void ReDateByNow(DbFamily family)
		{
			string arg = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
			using (DbCommand dbCommand = this.mDatabase.Connection.CreateCommand())
			{
				dbCommand.CommandText = string.Format("update [FamilyTable] set [Date]='{0}' where [GUID]='{1}'", arg, family.Guid.ToString());
				dbCommand.ExecuteNonQuery();
			}
		}

		public DbFamily this[Guid id]
		{
			get
			{
				DataTable dataTable = this.mDatabase.ExecQuery("select * from [FamilyTable] where [GUID]='" + id.ToString() + "'");
				if (dataTable != null && dataTable.Rows.Count > 0)
				{
					return this.DatatableToDbFamilyList(dataTable)[0];
				}
				return null;
			}
		}

		public DbFamily this[string name]
		{
			get
			{
				DataTable dataTable = this.mDatabase.ExecQuery("select * from [FamilyTable] where [FamilyName]='" + name + "'");
				if (dataTable != null && dataTable.Rows.Count > 0)
				{
					return this.DatatableToDbFamilyList(dataTable)[0];
				}
				return null;
			}
		}

		public List<DbFamily> this[Parent parent, CheckType checkType]
		{
			get
			{
				string sqlStr;
				if (checkType == CheckType.All)
				{
					sqlStr = string.Format("select * from [FamilyTable] where [ParentId]='{0}' ", parent.Guid.ToString());
				}
				else if (checkType == CheckType.Checked)
				{
					sqlStr = string.Format("select * from [FamilyTable] where [ParentId]='{0}' and [Check]='{1}'", parent.Guid.ToString(), 1);
				}
				else
				{
					sqlStr = string.Format("select * from [FamilyTable] where [ParentId]='{0}' and [Check]=!'{1}'", parent.Guid.ToString(), 1);
				}
				DataTable dataTable = this.mDatabase.ExecQuery(sqlStr);
				if (dataTable != null && dataTable.Rows.Count > 0)
				{
					return this.DatatableToDbFamilyList(dataTable);
				}
				return new List<DbFamily>();
			}
		}

		private List<DbFamily> DatatableToDbFamilyList(DataTable dt)
		{
			List<DbFamily> list = new List<DbFamily>();
			if (dt != null && dt.Rows.Count > 0)
			{
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					Image img = new Bitmap(new MemoryStream((byte[])dt.Rows[i]["FamilyImg"]));
					DbFamily dbFamily = new DbFamily(dt.Rows[i]["FamilyName"].ToString().Trim(), img, new Guid(dt.Rows[i]["GUID"].ToString()));
					dbFamily.Limit = Convert.ToBoolean(dt.Rows[i]["Limit"].ToString());
					dbFamily.Version = dt.Rows[i]["Version"].ToString().Trim();
					dbFamily.SetDate(Convert.ToDateTime(dt.Rows[i]["Date"].ToString()));
					dbFamily.Description = dt.Rows[i]["Description"].ToString().Trim();
					dbFamily.SetParentId(new Guid(dt.Rows[i]["ParentId"].ToString()));
					dbFamily.KeyName = dt.Rows[i]["KeyName"].ToString();
					dbFamily.SetDatabase(this.mDatabase);
					if (dt.Rows[i]["eID1"].ToString() != "")
					{
						dbFamily.ExData.Eid1 = Convert.ToInt32(dt.Rows[i]["eID1"].ToString());
					}
					if (dt.Rows[i]["eID2"].ToString() != "")
					{
						dbFamily.ExData.Eid2 = Convert.ToInt32(dt.Rows[i]["eID2"].ToString());
					}
					dbFamily.ExData.Tag1 = dt.Rows[i]["tag1"].ToString();
					dbFamily.ExData.Tag2 = dt.Rows[i]["tag2"].ToString();
					dbFamily.Check = (bool)dt.Rows[i]["Check"];
					dbFamily.Count = (int)dt.Rows[i]["Count"];
					if (dt.Rows[i]["UseDate"].ToString() != "")
					{
						dbFamily.SetUseDate(Convert.ToDateTime(dt.Rows[i]["UseDate"].ToString()));
					}
					list.Add(dbFamily);
				}
			}
			return list;
		}

		public List<DbFamily> this[Parent parent, int start, int end, CheckType checkType]
		{
			get
			{
				string sqlStr;
				if (checkType == CheckType.All)
				{
					sqlStr = string.Format("select * from [FamilyTable] where [ParentId]='{0}' limit {1} offset {2}", parent.Guid.ToString(), end - start, start);
				}
				else if (checkType == CheckType.Checked)
				{
					sqlStr = string.Format("select * from [FamilyTable] where [ParentId]='{0}'  and [Check]='{1}' limit {2} offset {3}", new object[]
					{
						parent.Guid.ToString(),
						1,
						end - start,
						start
					});
				}
				else
				{
					sqlStr = string.Format("select * from [FamilyTable] where [ParentId]='{0}'  and [Check]=!'{1}' limit {2} offset {3}", new object[]
					{
						parent.Guid.ToString(),
						1,
						end - start,
						start
					});
				}
				DataTable dataTable = this.mDatabase.ExecQuery(sqlStr);
				if (dataTable != null && dataTable.Rows.Count > 0)
				{
					return this.DatatableToDbFamilyList(dataTable);
				}
				return new List<DbFamily>();
			}
		}

		public List<DbFamily> GetAll()
		{
			DataTable dataTable = this.mDatabase.ExecQuery("select * from [FamilyTable]");
			if (dataTable != null && dataTable.Rows.Count > 0)
			{
				return this.DatatableToDbFamilyList(dataTable);
			}
			return new List<DbFamily>();
		}

		public bool Remove(DbFamily family)
		{
			DbTransaction dbTransaction = this.mDatabase.StartTransaction();
			bool result;
			try
			{
				this.mDatabase.ExecuteCommand("delete from [FamilyTable] where [GUID]='" + family.Guid.ToString() + "'", dbTransaction);
				this.mDatabase.FamilyFileTable.Remove(family, dbTransaction);
				this.mDatabase.FamilyImgTable.Remove(family, dbTransaction);
				this.mDatabase.FamilyParamTable.Remove(family, dbTransaction);
				this.mDatabase.TypeTable.Drop(family, dbTransaction);
				dbTransaction.Commit();
				result = true;
			}
			catch (Exception ex)
			{
				dbTransaction.Rollback();
				MessageBox.Show(ex.Message);
				result = false;
			}
			return result;
		}

		public List<DbFamily> SearchFamily(string familyName, int start, int end)
		{
			DataTable dataTable = this.mDatabase.ExecQuery(string.Concat(new string[]
			{
				"select * from [FamilyTable] where [FamilyName] like'%",
				familyName,
				"%' limit ",
				(end - start).ToString(),
				" offset ",
				start.ToString()
			}));
			if (dataTable != null && dataTable.Rows.Count > 0)
			{
				return this.DatatableToDbFamilyList(dataTable);
			}
			return new List<DbFamily>();
		}

		public List<DbFamily> SearchFamily(string familyName)
		{
			DataTable dataTable = this.mDatabase.ExecQuery("select * from [FamilyTable] where [FamilyName] like'%" + familyName + "%'");
			if (dataTable != null && dataTable.Rows.Count > 0)
			{
				return this.DatatableToDbFamilyList(dataTable);
			}
			return new List<DbFamily>();
		}

		public List<DbFamily> SearchFamily(List<string> keys)
		{
			string text = "'%" + keys[0];
			for (int i = 1; i < keys.Count; i++)
			{
				text = text + "%" + keys[i] + "%";
			}
			text += "'";
			DataTable dataTable = this.mDatabase.ExecQuery("select * from [FamilyTable] where [tag1] like " + text);
			if (dataTable != null && dataTable.Rows.Count > 0)
			{
				return this.DatatableToDbFamilyList(dataTable);
			}
			return new List<DbFamily>();
		}

		public int SearchFamilyCount(string familyName)
		{
			DataTable dataTable = this.mDatabase.ExecQuery("select * from [FamilyTable] where [FamilyName] like'%" + familyName + "%'");
			if (dataTable != null)
			{
				return dataTable.Rows.Count;
			}
			return 0;
		}

		public List<DbFamily> SearchFamily(DateTime startTime, DateTime endTime, int start, int end)
		{
			DataTable dataTable = this.mDatabase.ExecQuery(string.Format("select * from [FamilyTable] where [Date] BETWEEN '{0}' AND '{1}' limit {2} offset {3}", new object[]
			{
				startTime.ToString("yyyy-MM-dd"),
				endTime.ToString("yyyy-MM-dd"),
				(end - start).ToString(),
				start.ToString()
			}));
			if (dataTable != null && dataTable.Rows.Count > 0)
			{
				return this.DatatableToDbFamilyList(dataTable);
			}
			return new List<DbFamily>();
		}

		public List<DbFamily> SearchFamily(DateTime startTime, DateTime endTime)
		{
			DataTable dataTable = this.mDatabase.ExecQuery(string.Format("select * from [FamilyTable] where [Date] BETWEEN '{0}' AND '{1}'", startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
			if (dataTable != null && dataTable.Rows.Count > 0)
			{
				return this.DatatableToDbFamilyList(dataTable);
			}
			return new List<DbFamily>();
		}

		public int SearchFamilyCount(DateTime startTime, DateTime endTime)
		{
			DataTable dataTable = this.mDatabase.ExecQuery(string.Format("select * from [FamilyTable] where [Date] BETWEEN '{0}' AND '{1}'", startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
			if (dataTable != null)
			{
				return dataTable.Rows.Count;
			}
			return 0;
		}

		private SqliteDatabase mDatabase;
	}
}
